{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2172f664",
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load mid_homework.py\n",
    "'''\n",
    "一、修改bug\n",
    "在四个计算方法中将\n",
    "rate = (df.price - df.price.shift(1))/df.price.shift(1)\n",
    "改为\n",
    "rate = (temp.price - temp.price.shift(1))/temp.price.shift(1)\n",
    "\n",
    "原因：\n",
    "temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)\n",
    "语句中进行了时间相同取价格平均值的操作，合并了部分数据项。\n",
    "如果使用 df 进行后续计算会导致结果错误\n",
    "\n",
    "二、优化coding\n",
    "将rate = (temp.price - temp.price.shift(1))/temp.price.shift(1)\n",
    "改为\n",
    "temp_shift=temp.price.shift(1)\n",
    "rate = (temp.price - temp_shift)/temp_shift\n",
    "\n",
    "原因：\n",
    "减少数据移位操作，避免重复计算，提高计算效率\n",
    "'''\n",
    "\n",
    "\n",
    "\n",
    "#更改后代码如下：\n",
    "\n",
    "#引库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "#读取数据文件\n",
    "path='../../lecture-python/raw-data/bond_intraday_trade.csv'\n",
    "data = pd.read_csv(path)\n",
    "\n",
    "#提取cusip_id,trd_exctn_dt,trd_exctn_tm,rptd_pr四列\n",
    "data_use = data[['cusip_id','trd_exctn_dt','trd_exctn_tm','rptd_pr']]\n",
    "#将四列重新命名\n",
    "data_use.columns=['id','date','hour','price']\n",
    "\n",
    "#去除price == 0的项\n",
    "data_use2 = data_use.drop(index=(data_use.loc[(data_use['price']==0)].index))\n",
    "\n",
    "\n",
    "#为排序，将hour列数据调整为int型\n",
    "data_use2['hour_adj'] = data_use2['hour'].str.replace(':','').astype('int')\n",
    "#调整日期类型为 年，月，日\n",
    "data_use2['date'] = pd.to_datetime(data_use2['date'],format='%Y%m%d')\n",
    "\n",
    "\n",
    "#计算roll's measure\n",
    "#cov()可求得矩阵的协方差矩阵\n",
    "#第一种情况：if cov> 0,返回np.nan\n",
    "def roll_liquidity1(df):\n",
    "    global low_trade_times, positive_cov\n",
    "    # first： 时间相同，取价格 mean取平均值\n",
    "    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)\n",
    "    #shape()返回行数 only calculate when size > 3 \n",
    "    if temp.shape[0]>3:\n",
    "        #shift将数据向下移动一位\n",
    "        # !!改动：将df改为temp\n",
    "        temp_shift=temp.price.shift(1)\n",
    "        rate = (temp.price - temp_shift)/temp_shift\n",
    "        #rate从第二个到倒数第二个，从第三个到末尾 [0,1]取第一行第二列的数据\n",
    "        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na   \n",
    "        if c < 0:\n",
    "            return np.sqrt(-c)*2\n",
    "        else:\n",
    "            positive_cov += 1\n",
    "            return np.nan\n",
    "    low_trade_times += 1\n",
    "    return [np.nan]*4\n",
    "\n",
    "#第一种情况：if cov> 0,返回0\n",
    "def roll_liquidity2(df):\n",
    "    #global low_trade_times, positive_cov\n",
    "    # first： 时间相同，取价格mean\n",
    "    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)\n",
    "    # only calculate when size > 3\n",
    "    if temp.shape[0]>3:\n",
    "        temp_shift=temp.price.shift(1)\n",
    "        rate = (temp.price - temp_shift)/temp_shift\n",
    "        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na\n",
    "        if c < 0:\n",
    "            return np.sqrt(-c)*2\n",
    "        else:\n",
    "            #positive_cov += 1\n",
    "            return 0\n",
    "    #low_trade_times += 1\n",
    "    return np.nan   # 日内价格数量<3, 赋值np.nan, 以区分cov<0，事后可以方便改为0若有需要\n",
    "\n",
    "\n",
    "#第三种情况：if cov> 0,取平方根而不应用负号，并将结果视为负点差。\n",
    "def roll_liquidity3(df):\n",
    "    #global low_trade_times, positive_cov\n",
    "    # first： 时间相同，取价格mean\n",
    "    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)\n",
    "    # only calculate when size > 3\n",
    "    if temp.shape[0]>3:\n",
    "        temp_shift=temp.price.shift(1)\n",
    "        rate = (temp.price - temp_shift)/temp_shift\n",
    "        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na\n",
    "        if c < 0:\n",
    "            return np.sqrt(-c)*2\n",
    "        else:\n",
    "            #positive_cov += 1\n",
    "            return -np.sqrt(c)*2\n",
    "    #low_trade_times += 1\n",
    "    return np.nan # 日内价格数量<3, 赋值np.nan, 以区分cov<0，事后可以方便改为0若有需要\n",
    "\n",
    "#第四种情况：if cov> 0,将正协方差视为负数，从而生成正滚动点差估计值\n",
    "def roll_liquidity4(df):\n",
    "    #global low_trade_times, positive_cov\n",
    "    # first： 时间相同，取价格mean\n",
    "    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)\n",
    "    # only calculate when size > 3\n",
    "    if temp.shape[0]>3:\n",
    "        temp_shift=temp.price.shift(1)\n",
    "        rate = (temp.price - temp_shift)/temp_shift\n",
    "        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na\n",
    "        if c < 0:\n",
    "            return np.sqrt(-c)*2\n",
    "        else:\n",
    "            #positive_cov += 1\n",
    "            return np.sqrt(c)*2\n",
    "    #low_trade_times += 1\n",
    "    return np.nan # 日内价格数量<3, 赋值np.nan, 以区分cov<0，事后可以方便改为0若有需要\n",
    "\n",
    "low_trade_times = 0\n",
    "positive_cov = 0\n",
    "roll_daily = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity1),columns = ['roll_liquidity_method1'])\n",
    "roll_daily2 = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity2),columns = ['roll_liquidity_method2'])\n",
    "roll_daily3 = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity3),columns = ['roll_liquidity_method3'])\n",
    "roll_daily4 = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity4),columns = ['roll_liquidity_method4'])\n",
    "result = pd.concat([roll_daily,roll_daily2,roll_daily3,roll_daily4],axis = 1)\n",
    "result.reset_index(inplace=True)\n",
    "result.rename(columns={'id':'cusip_id'},inplace = True)\n",
    "result.to_csv('roll_daily_liquidity_measure_all.csv', index = False)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8900202",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
